<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL UNION 与 UNION ALL 完全指南</title>
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            background-color: #f8fafc;
            color: #1e293b;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #3b82f6 0%, #6366f1 100%);
        }
        .card-hover {
            transition: all 0.3s ease;
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04);
        }
        .code-block {
            background-color: #1e293b;
            border-left: 4px solid #3b82f6;
        }
        .drop-cap::first-letter {
            float: left;
            font-size: 3.5em;
            line-height: 0.8;
            margin-right: 0.1em;
            font-weight: 700;
            color: #3b82f6;
        }
    </style>
</head>
<body>
    <!-- Hero Section -->
    <div class="hero-gradient text-white py-20 px-4 sm:px-6 lg:px-8">
        <div class="max-w-7xl mx-auto">
            <div class="flex flex-col items-center text-center">
                <div class="inline-flex items-center justify-center bg-white/10 rounded-full px-4 py-1 mb-4">
                    <i class="fas fa-database mr-2"></i>
                    <span>SQL 操作符教程</span>
                </div>
                <h1 class="text-4xl md:text-5xl font-bold mb-6">UNION 与 UNION ALL</h1>
                <p class="text-xl max-w-3xl leading-relaxed opacity-90">
                    SQL 结果集合并操作的完整指南：从基础概念到高级用法
                </p>
            </div>
        </div>
    </div>

    <!-- Main Content -->
    <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8 py-16">
        <!-- Introduction -->
        <div class="mb-16">
            <p class="drop-cap text-lg leading-relaxed text-slate-700 mb-6">
                UNION 和 UNION ALL 是 SQL 中用于合并两个或多个 SELECT 查询结果的操作符。它们之间的主要区别在于是否去除重复的记录。理解它们的差异和适用场景对于编写高效的 SQL 查询至关重要。
            </p>
            
            <!-- Visualization -->
            <div class="bg-white rounded-xl shadow-lg p-6 mb-12">
                <h3 class="text-2xl font-semibold mb-6 text-center">UNION vs UNION ALL 对比</h3>
                <div class="mermaid">
                    flowchart TD
                    A[SQL 结果集合并] --> B[UNION]
                    A --> C[UNION ALL]
                    B --> D[自动去重]
                    B --> E[性能开销较大]
                    C --> F[保留所有记录]
                    C --> G[性能更优]
                </div>
            </div>
        </div>

        <!-- Concepts Section -->
        <div class="grid md:grid-cols-2 gap-8 mb-16">
            <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                <div class="bg-blue-600 text-white px-6 py-4">
                    <h2 class="text-xl font-semibold flex items-center">
                        <i class="fas fa-filter mr-2"></i>
                        UNION 基本概念
                    </h2>
                </div>
                <div class="p-6">
                    <p class="text-slate-700 mb-4">
                        UNION 合并两个或多个 SELECT 查询的结果集，并<strong class="text-blue-600">自动去除重复</strong>的记录。
                    </p>
                    <ul class="space-y-2">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                            <span>适用于需要去重的场景</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                            <span>需要进行排序和去重操作</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                            <span>在大数据集上性能开销较大</span>
                        </li>
                    </ul>
                </div>
            </div>

            <div class="bg-white rounded-xl shadow-md overflow-hidden card-hover">
                <div class="bg-indigo-600 text-white px-6 py-4">
                    <h2 class="text-xl font-semibold flex items-center">
                        <i class="fas fa-layer-group mr-2"></i>
                        UNION ALL 基本概念
                    </h2>
                </div>
                <div class="p-6">
                    <p class="text-slate-700 mb-4">
                        UNION ALL 合并两个或多个 SELECT 查询的结果集，<strong class="text-indigo-600">保留所有记录</strong>，不去除重复。
                    </p>
                    <ul class="space-y-2">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                            <span>性能比 UNION 更优</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                            <span>不需要排序和去重操作</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle text-indigo-500 mt-1 mr-2"></i>
                            <span>适用于需要保留所有记录的场景</span>
                        </li>
                    </ul>
                </div>
            </div>
        </div>

        <!-- Performance Comparison -->
        <div class="bg-white rounded-xl shadow-lg p-8 mb-16">
            <h2 class="text-3xl font-semibold mb-6 flex items-center">
                <i class="fas fa-tachometer-alt text-blue-500 mr-3"></i>
                性能对比
            </h2>
            
            <div class="grid md:grid-cols-2 gap-8 mb-8">
                <div>
                    <h3 class="text-xl font-semibold mb-4 text-blue-600">UNION 性能特点</h3>
                    <div class="bg-blue-50 rounded-lg p-6">
                        <ul class="space-y-3">
                            <li class="flex items-start">
                                <div class="bg-blue-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-stopwatch text-blue-600"></i>
                                </div>
                                <span>需要对结果集进行排序和去重操作</span>
                            </li>
                            <li class="flex items-start">
                                <div class="bg-blue-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-server text-blue-600"></i>
                                </div>
                                <span>处理大数据集时性能降低显著</span>
                            </li>
                            <li class="flex items-start">
                                <div class="bg-blue-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-memory text-blue-600"></i>
                                </div>
                                <span>内存和 CPU 使用率较高</span>
                            </li>
                        </ul>
                    </div>
                </div>
                
                <div>
                    <h3 class="text-xl font-semibold mb-4 text-indigo-600">UNION ALL 性能特点</h3>
                    <div class="bg-indigo-50 rounded-lg p-6">
                        <ul class="space-y-3">
                            <li class="flex items-start">
                                <div class="bg-indigo-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-bolt text-indigo-600"></i>
                                </div>
                                <span>不进行去重操作，性能更高</span>
                            </li>
                            <li class="flex items-start">
                                <div class="bg-indigo-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-rocket text-indigo-600"></i>
                                </div>
                                <span>直接将结果集连接起来</span>
                            </li>
                            <li class="flex items-start">
                                <div class="bg-indigo-100 p-2 rounded-full mr-3">
                                    <i class="fas fa-chart-line text-indigo-600"></i>
                                </div>
                                <span>适合大数据量处理</span>
                            </li>
                        </ul>
                    </div>
                </div>
            </div>
            
            <div class="bg-slate-50 rounded-lg p-6">
                <h4 class="font-semibold text-lg mb-3">性能优化建议</h4>
                <p class="text-slate-700">
                    在不需要去重的情况下，总是优先使用 <code class="bg-slate-200 px-2 py-1 rounded">UNION ALL</code>，它可以提供更高的性能。只有在确实需要去除重复记录时，才使用 <code class="bg-slate-200 px-2 py-1 rounded">UNION</code>。
                </p>
            </div>
        </div>

        <!-- Usage Examples -->
        <div class="mb-16">
            <h2 class="text-3xl font-semibold mb-8 flex items-center">
                <i class="fas fa-code text-blue-500 mr-3"></i>
                用法示例
            </h2>
            
            <div class="mb-10">
                <h3 class="text-2xl font-semibold mb-4">假设有两个表 table1 和 table2</h3>
                
                <div class="grid md:grid-cols-2 gap-6 mb-8">
                    <div>
                        <div class="flex items-center bg-slate-800 text-white px-4 py-2 rounded-t-lg">
                            <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                            <span class="text-sm">table1</span>
                        </div>
                        <div class="code-block text-gray-200 p-4 rounded-b-lg overflow-x-auto">
                            <pre><code>id | name
---|-----
 1 | Alice
 2 | Bob
 3 | Charlie</code></pre>
                        </div>
                    </div>
                    
                    <div>
                        <div class="flex items-center bg-slate-800 text-white px-4 py-2 rounded-t-lg">
                            <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                            <span class="text-sm">table2</span>
                        </div>
                        <div class="code-block text-gray-200 p-4 rounded-b-lg overflow-x-auto">
                            <pre><code>id | name
---|-----
 2 | Bob
 3 | Charlie
 4 | David</code></pre>
                        </div>
                    </div>
                </div>
                
                <!-- UNION Example -->
                <div class="mb-10">
                    <h4 class="text-xl font-semibold mb-3 flex items-center">
                        <i class="fas fa-link text-blue-500 mr-2"></i>
                        UNION 示例
                    </h4>
                    <div class="bg-white rounded-lg overflow-hidden shadow">
                        <div class="flex items-center bg-slate-800 text-white px-4 py-2">
                            <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                            <span class="text-sm">SQL 查询</span>
                        </div>
                        <div class="code-block text-gray-200 p-4 overflow-x-auto">
                            <pre><code>SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;</code></pre>
                        </div>
                        
                        <div class="border-t border-gray-200">
                            <div class="flex items-center bg-slate-800 text-white px-4 py-2">
                                <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                                <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                                <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                                <span class="text-sm">结果集</span>
                            </div>
                            <div class="code-block text-gray-200 p-4 overflow-x-auto">
                                <pre><code>id | name
---|-----
 1 | Alice
 2 | Bob
 3 | Charlie
 4 | David</code></pre>
                            </div>
                        </div>
                    </div>
                    <p class="mt-3 text-slate-600">
                        UNION 自动去除了 Bob 和 Charlie 的重复记录。
                    </p>
                </div>
                
                <!-- UNION ALL Example -->
                <div>
                    <h4 class="text-xl font-semibold mb-3 flex items-center">
                        <i class="fas fa-link text-indigo-500 mr-2"></i>
                        UNION ALL 示例
                    </h4>
                    <div class="bg-white rounded-lg overflow-hidden shadow">
                        <div class="flex items-center bg-slate-800 text-white px-4 py-2">
                            <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                            <span class="text-sm">SQL 查询</span>
                        </div>
                        <div class="code-block text-gray-200 p-4 overflow-x-auto">
                            <pre><code>SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;</code></pre>
                        </div>
                        
                        <div class="border-t border-gray-200">
                            <div class="flex items-center bg-slate-800 text-white px-4 py-2">
                                <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                                <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                                <div class="w-3 h-3 bg-green-500 rounded-full mr-2"></div>
                                <span class="text-sm">结果集</span>
                            </div>
                            <div class="code-block text-gray-200 p-4 overflow-x-auto">
                                <pre><code>id | name
---|-----
 1 | Alice
 2 | Bob
 3 | Charlie
 2 | Bob
 3 | Charlie
 4 | David</code></pre>
                            </div>
                        </div>
                    </div>
                    <p class="mt-3 text-slate-600">
                        UNION ALL 包含了 Bob 和 Charlie 的重复记录。
                    </p>
                </div>
            </div>
        </div>

        <!-- Use Cases -->
        <div class="mb-16">
            <h2 class="text-3xl font-semibold mb-8 flex items-center">
                <i class="fas fa-lightbulb text-yellow-500 mr-3"></i>
                使用场景
            </h2>
            
            <div class="grid md:grid-cols-2 gap-8">
                <div class="bg-white rounded-xl shadow-md p-6 card-hover">
                    <div class="flex items-center mb-4">
                        <div class="bg-blue-100 p-3 rounded-full mr-4">
                            <i class="fas fa-filter text-blue-600 text-xl"></i>
                        </div>
                        <h3 class="text-xl font-semibold">UNION 适用场景</h3>
                    </div>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <div class="bg-blue-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-blue-600 text-xs"></i>
                            </div>
                            <span>需要确保结果集中不包含重复记录时</span>
                        </li>
                        <li class="flex items-start">
                            <div class="bg-blue-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-blue-600 text-xs"></i>
                            </div>
                            <span>需要对合并的结果进行数据清理</span>
                        </li>
                        <li class="flex items-start">
                            <div class="bg-blue-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-blue-600 text-xs"></i>
                            </div>
                            <span>当数据完整性比性能更重要时</span>
                        </li>
                    </ul>
                </div>
                
                <div class="bg-white rounded-xl shadow-md p-6 card-hover">
                    <div class="flex items-center mb-4">
                        <div class="bg-indigo-100 p-3 rounded-full mr-4">
                            <i class="fas fa-layer-group text-indigo-600 text-xl"></i>
                        </div>
                        <h3 class="text-xl font-semibold">UNION ALL 适用场景</h3>
                    </div>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <div class="bg-indigo-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-indigo-600 text-xs"></i>
                            </div>
                            <span>确信查询结果不会产生重复记录时</span>
                        </li>
                        <li class="flex items-start">
                            <div class="bg-indigo-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-indigo-600 text-xs"></i>
                            </div>
                            <span>需要保留所有记录的日志分析或统计场景</span>
                        </li>
                        <li class="flex items-start">
                            <div class="bg-indigo-100 p-1 rounded-full mr-3 mt-1">
                                <i class="fas fa-check text-indigo-600 text-xs"></i>
                            </div>
                            <span>性能优化是首要考虑因素时</span>
                        </li>
                    </ul>
                </div>
            </div>
        </div>

        <!-- Additional Notes -->
        <div class="bg-white rounded-xl shadow-lg p-8">
            <h2 class="text-3xl font-semibold mb-6 flex items-center">
                <i class="fas fa-exclamation-circle text-orange-500 mr-3"></i>
                注意事项
            </h2>
            
            <div class="grid md:grid-cols-2 gap-8">
                <div>
                    <h3 class="text-xl font-semibold mb-4 text-slate-700">结果排序</h3>
                    <div class="bg-slate-50 rounded-lg p-6">
                        <p class="text-slate-700 mb-3">
                            UNION 和 UNION ALL 的结果集都可以使用 <code class="bg-slate-200 px-2 py-1 rounded">ORDER BY</code> 子句来排序。
                        </p>
                        <p class="text-slate-700">
                            注意，<code class="bg-slate-200 px-2 py-1 rounded">ORDER BY</code> 通常是应用在整个合并后的结果集上，而不是单独的 SELECT 子句中。
                        </p>
                    </div>
                </div>
                
                <div>
                    <h3 class="text-xl font-semibold mb-4 text-slate-700">错误处理</h3>
                    <div class="bg-slate-50 rounded-lg p-6">
                        <p class="text-slate-700">
                            UNION 和 UNION ALL 中的所有 SELECT 子句必须具有<strong>相同数量的列</strong>，并且相应列的<strong>数据类型需要兼容</strong>。否则会导致错误。
                        </p>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>